package com.easidea.tim.action.util;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;

import com.easidea.tim.action.base.BaseAction;
import com.easidea.tim.dto.report.StatByProductDto;
import com.easidea.tim.service.saleorder.SaleOrderService;
import com.opensymphony.webwork.ServletActionContext;

public class StatDownloadAction extends BaseAction{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -3730993654172703875L;

	private static final Logger log = Logger.getLogger(StatDownloadAction.class);

	private String filename;
	private Integer year;
	private Integer month;
	private String startDate;
	private String endDate;
	private SaleOrderService saleOrderService;
	private String customerName;
	private String staffName;
	
	/**
	 * 导出产品销售统计
	 * @return
	 */
	public String downloadStatByProductListingArray() {
		try {
			if (saleOrderService != null) {
				List<StatByProductDto> list = saleOrderService.statByProductReport(startDate,endDate);
				filename = URLEncoder.encode("销售统计报表-" + getDateStr() + ".xls","UTF-8");

				HttpServletResponse response = ServletActionContext.getResponse();
				response.setContentType("application/octet-stream;charset=utf-8");
				response.setCharacterEncoding("utf-8");
				response.setHeader("Content-Disposition","attachment;filename=\"" + filename + "\"");
				response.setBufferSize(4096);
				OutputStream outStream = response.getOutputStream();
				excelStatByProductReport(list, outStream);
			}
		} catch (Exception e) {
			log.error("output excel error : ", e);
		}
		return null;
	}
	
	/**
	 * 导出客户销售统计
	 * @return
	 */
	public String downloadStatByCustomerListingArray() {
		try {
			if (saleOrderService != null) {
				List<StatByProductDto> list = saleOrderService.statByCustomerReport("","",startDate,endDate);
				filename = URLEncoder.encode("客户统计报表-" + getDateStr() + ".xls","UTF-8");

				HttpServletResponse response = ServletActionContext.getResponse();
				response.setContentType("application/octet-stream;charset=utf-8");
				response.setCharacterEncoding("utf-8");
				response.setHeader("Content-Disposition","attachment;filename=\"" + filename + "\"");
				response.setBufferSize(4096);
				OutputStream outStream = response.getOutputStream();
				excelStatByCustomerReport(list, outStream);
			}
		} catch (Exception e) {
			log.error("output excel error : ", e);
		}
		return null;
	}
	
	/**
	 * 下载客户汇总报表
	 * @return
	 */
	public String downloadAchieveByCustomerListingArray() {
		try {
			if (saleOrderService != null) {
				List<StatByProductDto> list = saleOrderService.statByCustomerReport(customerName,staffName,startDate,endDate);
				filename = URLEncoder.encode("客户汇总统计报表-" + getDateStr() + ".xls","UTF-8");

				HttpServletResponse response = ServletActionContext.getResponse();
				response.setContentType("application/octet-stream;charset=utf-8");
				response.setCharacterEncoding("utf-8");
				response.setHeader("Content-Disposition","attachment;filename=\"" + filename + "\"");
				response.setBufferSize(4096);
				OutputStream outStream = response.getOutputStream();
				excelAchieveByCustomerReport(list, outStream,staffName);
			}
		} catch (Exception e) {
			log.error("output excel error : ", e);
		}
		return null;
	}
	
	/**
	 * 导出产品销售月报
	 * @return
	 */
	public String downloadStatByProductMonthArray() {
		try {
			if (saleOrderService != null) {
				List<StatByProductDto> list = saleOrderService.statByProductMonth(year, month);
				filename = URLEncoder.encode("产品销售统计月报表-" + getDateStr() + ".xls","UTF-8");

				HttpServletResponse response = ServletActionContext.getResponse();
				response.setContentType("application/octet-stream;charset=utf-8");
				response.setCharacterEncoding("utf-8");
				response.setHeader("Content-Disposition","attachment;filename=\"" + filename + "\"");
				response.setBufferSize(4096);
				OutputStream outStream = response.getOutputStream();
				excelStatByProductMonthReport(list, outStream);
			}
		} catch (Exception e) {
			log.error("output excel error : ", e);
		}
		return null;
	}
	
	/**
	 * 导出销售人员业绩月报
	 * @return
	 */
	public String downloadStatByCreatorMonthArray() {
		try {
			if (saleOrderService != null) {
				List<StatByProductDto> list = saleOrderService.statByCreatorMonth(startDate,endDate);
				filename = URLEncoder.encode("销售员业绩统计报表-" + getDateStr() + ".xls","UTF-8");

				HttpServletResponse response = ServletActionContext.getResponse();
				response.setContentType("application/octet-stream;charset=utf-8");
				response.setCharacterEncoding("utf-8");
				response.setHeader("Content-Disposition","attachment;filename=\"" + filename + "\"");
				response.setBufferSize(4096);
				OutputStream outStream = response.getOutputStream();
				excelStatByCreatorMonthReport(list, outStream);
			}
		} catch (Exception e) {
			log.error("output excel error : ", e);
		}
		return null;
	}
	/**
	 * 导出产品销售统计excel
	 * @param list
	 * @param out
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void excelStatByProductReport(List<StatByProductDto> list,
			OutputStream out) throws IOException, RowsExceededException,
			WriteException {
		double pitotal=0;
		double metertotal=0;
		double moneytotal=0;
		if (list != null && list.size() > 0 && out != null) {
			WritableWorkbook excel = Workbook.createWorkbook(out);
			WritableSheet sheet = excel.createSheet("产品销售统计分析", 0);

			sheet.mergeCells(0, 0, 4, 0);

			WritableFont boldFont = new WritableFont(WritableFont
					.createFont("黑体"), 18, WritableFont.BOLD);
			WritableCellFormat boldFontFormat = new WritableCellFormat(boldFont);

			Label title = new Label(0, 0, "产品销售统计分析报表" + "【"+startDate + "至" + endDate + "】", boldFontFormat);
			sheet.addCell(title);
			
			WritableFont headerFont = new WritableFont(WritableFont.createFont("黑体"),14,WritableFont.BOLD);
			WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
			

			Label columnHeader1 = new Label(0, 2, "产品名称", headerCellFormat);
			Label columnHeader2 = new Label(1, 2, "色号", headerCellFormat);
			Label columnHeader3 = new Label(2, 2, "匹数", headerCellFormat);
			Label columnHeader4 = new Label(3, 2, "米数（米）", headerCellFormat);
			Label columnHeader5 = new Label(4, 2, "金额（元）", headerCellFormat);


			sheet.addCell(columnHeader1);
			sheet.addCell(columnHeader2);
			sheet.addCell(columnHeader3);
			sheet.addCell(columnHeader4);
			sheet.addCell(columnHeader5);

			DecimalFormat df = new DecimalFormat("###.000");
			DecimalFormat dfone = new DecimalFormat("###.0");
			int rowIndex = 3;
			for (StatByProductDto dto : list) {
				if (dto != null) {
					Label name = new Label(0, rowIndex, dto.getName());
					Label color = new Label(1, rowIndex, dto.getColor());
					Number piNum = new Number(2, rowIndex, dto.getPiNum());
					Number meterNum = new Number(3, rowIndex, Double.parseDouble(dfone.format(dto.getMeterNum())));
					Number moneyNum = new Number(4, rowIndex, this.getNumerReg(Double.parseDouble(df.format(dto.getMoneyNum()))));


					sheet.addCell(name);
					sheet.addCell(color);
					sheet.addCell(piNum);
					sheet.addCell(meterNum);
					sheet.addCell(moneyNum);
					
					pitotal +=dto.getPiNum();
					metertotal +=dto.getMeterNum();
					moneytotal +=dto.getMoneyNum();
					rowIndex++;
				}
			}
			
			Label total = new Label(1,rowIndex,"总计");
			Number piString = new Number(2,rowIndex,pitotal);
			Number meterString = new Number(3,rowIndex,Double.parseDouble(dfone.format(metertotal)));
			Number moneyString = new Number(4,rowIndex,this.getNumerReg(Double.parseDouble(df.format(moneytotal))));
			
			sheet.addCell(total);
			sheet.addCell(piString);
			sheet.addCell(meterString);
			sheet.addCell(moneyString);
			
			excel.write();
			excel.close();
		}
	}
	
	/**
	 * 客户销售统计导出excel
	 * @param list
	 * @param out
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void excelStatByCustomerReport(List<StatByProductDto> list,
			OutputStream out) throws IOException, RowsExceededException,
			WriteException {
		double pitotal=0;
		double metertotal=0;
		double moneytotal=0;
		if (list != null && list.size() > 0 && out != null) {
			WritableWorkbook excel = Workbook.createWorkbook(out);
			WritableSheet sheet = excel.createSheet("客户销售统计分析", 0);

			sheet.mergeCells(0, 0, 4, 0);

			WritableFont boldFont = new WritableFont(WritableFont
					.createFont("黑体"), 18, WritableFont.BOLD);
			WritableCellFormat boldFontFormat = new WritableCellFormat(boldFont);

			Label title = new Label(0, 0, "客户销售统计分析报表" + "【"+startDate + "至" + endDate + "】", boldFontFormat);
			sheet.addCell(title);

			WritableFont headerFont = new WritableFont(WritableFont.createFont("黑体"),14,WritableFont.BOLD);
			WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
			

			Label columnHeader1 = new Label(0, 2, "客户名称", headerCellFormat);
			Label columnHeader2 = new Label(1, 2, "品名", headerCellFormat);
			Label columnHeader3 = new Label(2, 2, "色号", headerCellFormat);
			Label columnHeader4 = new Label(3, 2, "匹数", headerCellFormat);
			Label columnHeader5 = new Label(4, 2, "米数（米）", headerCellFormat);
			Label columnHeader6 = new Label(5, 2, "金额（元）", headerCellFormat);

			sheet.addCell(columnHeader1);
			sheet.addCell(columnHeader2);
			sheet.addCell(columnHeader3);
			sheet.addCell(columnHeader4);
			sheet.addCell(columnHeader5);
			sheet.addCell(columnHeader6);

			DecimalFormat df = new DecimalFormat("###.000");
			DecimalFormat dfone = new DecimalFormat("###.0");

			int rowIndex = 3;
			for (StatByProductDto dto : list) {
				if (dto != null) {
					int i = 0;
//					double cuspitotal=0;
//					double cusmetertotal=0;
//					double cusmoneytotal=0;
					List<StatByProductDto> cuslist = saleOrderService.getProductSummeryByCustomer(startDate, endDate, dto.getColor());
					for(StatByProductDto cusdto :cuslist){
						if(cusdto !=null){
							
							Label cusName =  new Label(0, rowIndex, dto.getName());
							Label productName = new Label(1,rowIndex,cusdto.getName());
							Label productColor = new Label(2,rowIndex,cusdto.getColor());
							Number cuspi = new Number(3,rowIndex,cusdto.getPiNum());
							Number cusmeter = new Number(4,rowIndex,Double.parseDouble(dfone.format(cusdto.getMeterNum())));
							Number cusmoney = new Number(5,rowIndex,this.getNumerReg(Double.parseDouble(df.format(cusdto.getMoneyNum()))));
							
//							cuspitotal +=dto.getPiNum();
//							cusmetertotal +=dto.getMeterNum();
//							cusmoneytotal +=dto.getMoneyNum();
							
							if(i == 0){
								sheet.addCell(cusName);
							}
							sheet.addCell(productName);
							sheet.addCell(productColor);
							sheet.addCell(cuspi);
							sheet.addCell(cusmeter);
							sheet.addCell(cusmoney);
							rowIndex++;
							i++;
						}
					}
					
					Label name = new Label(0, rowIndex,"小计");
					//Label color = new Label(1, rowIndex, dto.getColor());
					Number piNum = new Number(1, rowIndex, dto.getPiNum());
					Number meterNum = new Number(2, rowIndex, Double.parseDouble(dfone.format(dto.getMeterNum())));
					Number moneyNum = new Number(3, rowIndex, this.getNumerReg(Double.parseDouble(df.format(dto.getMoneyNum()))));


					sheet.addCell(name);
					//sheet.addCell(color);
					sheet.addCell(piNum);
					sheet.addCell(meterNum);
					sheet.addCell(moneyNum);
					


					rowIndex++;
					
					pitotal +=dto.getPiNum();
					metertotal +=dto.getMeterNum();
					moneytotal +=dto.getMoneyNum();
				}
			}
			
			Label total = new Label(0,rowIndex,"总计");
			Number piString = new Number(1,rowIndex,pitotal);
			Number meterString = new Number(2,rowIndex,Double.parseDouble(dfone.format(metertotal)));
			Number moneyString = new Number(3,rowIndex,this.getNumerReg(Double.parseDouble(df.format(moneytotal))));
			
			sheet.addCell(total);
			sheet.addCell(piString);
			sheet.addCell(meterString);
			sheet.addCell(moneyString);
			excel.write();
			excel.close();
		}
	}
	
	private void excelStatByProductMonthReport(List<StatByProductDto> list,
			OutputStream out) throws IOException, RowsExceededException,
			WriteException {
		double pitotal=0;
		double metertotal=0;
		double moneytotal=0;
		if (list != null && list.size() > 0 && out != null) {
			WritableWorkbook excel = Workbook.createWorkbook(out);
			WritableSheet sheet = excel.createSheet("产品销售统计月报表", 0);

			sheet.mergeCells(0, 0, 4, 0);

			WritableFont boldFont = new WritableFont(WritableFont
					.createFont("黑体"), 18, WritableFont.BOLD);
			WritableCellFormat boldFontFormat = new WritableCellFormat(boldFont);

			Label title = new Label(0, 0, "产品销售统计分析月报表"  + "  [" + year + "年"+ month + "月]", boldFontFormat);
			sheet.addCell(title);
			
			WritableFont headerFont = new WritableFont(WritableFont.createFont("黑体"),14,WritableFont.BOLD);
			WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
			

			Label columnHeader1 = new Label(0, 2, "产品名称", headerCellFormat);
			Label columnHeader2 = new Label(1, 2, "色号", headerCellFormat);
			Label columnHeader3 = new Label(2, 2, "匹数", headerCellFormat);
			Label columnHeader4 = new Label(3, 2, "米数（米）", headerCellFormat);
			Label columnHeader5 = new Label(4, 2, "金额（元）", headerCellFormat);


			sheet.addCell(columnHeader1);
			sheet.addCell(columnHeader2);
			sheet.addCell(columnHeader3);
			sheet.addCell(columnHeader4);
			sheet.addCell(columnHeader5);

			DecimalFormat df = new DecimalFormat("###.000");
			DecimalFormat dfone = new DecimalFormat("###.0");
			int rowIndex = 3;
			for (StatByProductDto dto : list) {
				if (dto != null) {
					Label name = new Label(0, rowIndex, dto.getName());
					Label color = new Label(1, rowIndex, dto.getColor());
					Number piNum = new Number(2, rowIndex, dto.getPiNum());
					Number meterNum = new Number(3, rowIndex, Double.parseDouble(dfone.format(dto.getMeterNum())));
					Number moneyNum = new Number(4, rowIndex, this.getNumerReg(Double.parseDouble(df.format(dto.getMoneyNum()))));


					sheet.addCell(name);
					sheet.addCell(color);
					sheet.addCell(piNum);
					sheet.addCell(meterNum);
					sheet.addCell(moneyNum);
					
					pitotal +=dto.getPiNum();
					metertotal +=dto.getMeterNum();
					moneytotal +=dto.getMoneyNum();

					rowIndex++;
				}
			}
			Label total = new Label(1,rowIndex,"总计");
			Number piString = new Number(2,rowIndex,pitotal);
			Number meterString = new Number(3,rowIndex,Double.parseDouble(dfone.format(metertotal)));
			Number moneyString = new Number(4,rowIndex,this.getNumerReg(Double.parseDouble(df.format(moneytotal))));
			
			sheet.addCell(total);
			sheet.addCell(piString);
			sheet.addCell(meterString);
			sheet.addCell(moneyString);
			
			excel.write();
			excel.close();
		}
	}
	
	private void excelStatByCreatorMonthReport(List<StatByProductDto> list,
			OutputStream out) throws IOException, RowsExceededException,
			WriteException {
		double pitotal=0;
		double metertotal=0;
		double moneytotal=0;
		if (list != null && list.size() > 0 && out != null) {
			WritableWorkbook excel = Workbook.createWorkbook(out);
			WritableSheet sheet = excel.createSheet("销售员业绩统计报表", 0);

			sheet.mergeCells(0, 0, 4, 0);

			WritableFont boldFont = new WritableFont(WritableFont
					.createFont("黑体"), 18, WritableFont.BOLD);
			WritableCellFormat boldFontFormat = new WritableCellFormat(boldFont);

			Label title = new Label(0, 0, "销售员业绩统计报表"  + "【"+startDate + "至" + endDate + "】", boldFontFormat);
			sheet.addCell(title);

			WritableFont headerFont = new WritableFont(WritableFont.createFont("黑体"),14,WritableFont.BOLD);
			WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
			

			Label columnHeader1 = new Label(0, 2, "销售员", headerCellFormat);
			Label columnHeader2 = new Label(1, 2, "产品名称", headerCellFormat);
			Label columnHeader3 = new Label(2, 2, "匹数", headerCellFormat);
			Label columnHeader4 = new Label(3, 2, "米数（米）", headerCellFormat);
			Label columnHeader5 = new Label(4, 2, "金额（元）", headerCellFormat);


			sheet.addCell(columnHeader1);
			sheet.addCell(columnHeader2);
			sheet.addCell(columnHeader3);
			sheet.addCell(columnHeader4);
			sheet.addCell(columnHeader5);

			DecimalFormat df = new DecimalFormat("###.000");
			DecimalFormat dfone = new DecimalFormat("###.0");

			int rowIndex = 3;
			for (StatByProductDto dto : list) {
				if (dto != null) {
					pitotal +=dto.getPiNum();
					metertotal +=dto.getMeterNum();
					moneytotal +=dto.getMoneyNum();
					int i =0;
					List<StatByProductDto> cuslist = saleOrderService.getProductSummeryByCreator(startDate, endDate, dto.getName());
					for(StatByProductDto cusdto :cuslist){
						if(cusdto !=null){
							
							Label name = new Label(0, rowIndex, dto.getName());
							Label productName = new Label(1,rowIndex,cusdto.getName());
							Number cuspi = new Number(2,rowIndex,cusdto.getPiNum());
							Number cusmeter = new Number(3,rowIndex,Double.parseDouble(dfone.format(cusdto.getMeterNum())));
							Number cusmoney = new Number(4,rowIndex,this.getNumerReg(Double.parseDouble(df.format(cusdto.getMoneyNum()))));
							
//							cuspitotal +=dto.getPiNum();
//							cusmetertotal +=dto.getMeterNum();
//							cusmoneytotal +=dto.getMoneyNum();
							
							if(i ==0){
								sheet.addCell(name);
							}
							sheet.addCell(productName);
							sheet.addCell(cuspi);
							sheet.addCell(cusmeter);
							sheet.addCell(cusmoney);
							rowIndex++;
							i++;
						}
					}
				}
				
				Label total = new Label(1, rowIndex, "小计");
				//Label color = new Label(1, rowIndex, dto.getColor());
				Number piNum = new Number(2, rowIndex, dto.getPiNum());
				Number meterNum = new Number(3, rowIndex, Double.parseDouble(dfone.format(dto.getMeterNum())));
				Number moneyNum = new Number(4, rowIndex, this.getNumerReg(Double.parseDouble(df.format(dto.getMoneyNum()))));


				sheet.addCell(total);
				//sheet.addCell(color);
				sheet.addCell(piNum);
				sheet.addCell(meterNum);
				sheet.addCell(moneyNum);
			}
			Label total = new Label(1,rowIndex,"总计");
			Number piString = new Number(2,rowIndex,pitotal);
			Number meterString = new Number(3,rowIndex,Double.parseDouble(dfone.format(metertotal)));
			Number moneyString = new Number(4,rowIndex,this.getNumerReg(Double.parseDouble(df.format(moneytotal))));
			
			sheet.addCell(total);
			sheet.addCell(piString);
			sheet.addCell(meterString);
			sheet.addCell(moneyString);
			
			excel.write();
			excel.close();
		}
	}

	/**
	 * 导出客户汇总报表
	 * @param list
	 * @param out
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void excelAchieveByCustomerReport(List<StatByProductDto> list,
			OutputStream out,String staffName) throws IOException, RowsExceededException,
			WriteException {
		double pitotal=0;
		double metertotal=0;
		double moneytotal=0;
		if (list != null && list.size() > 0 && out != null) {
			WritableWorkbook excel = Workbook.createWorkbook(out);
			WritableSheet sheet = excel.createSheet("客户汇总统计分析", 0);

			sheet.mergeCells(0, 0, 4, 0);

			WritableFont boldFont = new WritableFont(WritableFont
					.createFont("黑体"), 18, WritableFont.BOLD);
			WritableCellFormat boldFontFormat = new WritableCellFormat(boldFont);

			Label title = new Label(0, 0, "客户汇总统计分析报表" + "【"+startDate + "至" + endDate + "】", boldFontFormat);
			sheet.addCell(title);

			WritableFont headerFont = new WritableFont(WritableFont.createFont("黑体"),14,WritableFont.BOLD);
			WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
			

			Label columnHeader1 = new Label(0, 2, "客户名称", headerCellFormat);
			//Label columnHeader2 = new Label(1, 2, "色号", headerCellFormat);
			Label columnHeader2 = new Label(1, 2, "产品名称", headerCellFormat);
			Label columnHeader3 = new Label(2, 2, "匹数（匹）", headerCellFormat);
			Label columnHeader4 = new Label(3, 2, "米数（米）", headerCellFormat);
			Label columnHeader5 = new Label(4, 2, "金额（元）", headerCellFormat);
			
			sheet.addCell(columnHeader1);
			sheet.addCell(columnHeader2);
			sheet.addCell(columnHeader3);
			sheet.addCell(columnHeader4);
			sheet.addCell(columnHeader5);
			//sheet.addCell(columnHeader5);


			DecimalFormat df = new DecimalFormat("###.000");
			DecimalFormat dfone = new DecimalFormat("###.0");
			int rowIndex = 3;
			for (StatByProductDto dto : list) {
				if (dto != null) {
					int i = 0;
					List<StatByProductDto> cuslist = saleOrderService.getSimpleProductSummeryByCustomer(startDate, endDate, dto.getColor(),staffName);
					for(StatByProductDto cusdto :cuslist){
						if(cusdto !=null){
							
						//	Label name = new Label(0, rowIndex, dto.getName());
							Label productName = new Label(1,rowIndex,cusdto.getName());
							Number cuspi = new Number(2,rowIndex,cusdto.getPiNum());
							Number cusmeter = new Number(3,rowIndex,Double.parseDouble(dfone.format(cusdto.getMeterNum())));
							Number cusmoney = new Number(4,rowIndex,this.getNumerReg(Double.parseDouble(df.format(cusdto.getMoneyNum()))));
							
//							if(i==0){
//								sheet.addCell(name);
//							}
							sheet.addCell(productName);
							sheet.addCell(cuspi);
							sheet.addCell(cusmeter);
							sheet.addCell(cusmoney);
							rowIndex++;
							i++;
						}
					}

					pitotal +=dto.getPiNum();
					metertotal +=dto.getMeterNum();
					moneytotal +=dto.getMoneyNum();
				}					
				
				Label name = new Label(0, rowIndex, dto.getName());
				Label total = new Label(1, rowIndex, "小计");
				//Label color = new Label(1, rowIndex, dto.getColor());
				Number piNum = new Number(2, rowIndex, dto.getPiNum());
				Number meterNum = new Number(3, rowIndex, Double.parseDouble(dfone.format(dto.getMeterNum())));
				Number moneyNum = new Number(4, rowIndex, this.getNumerReg(Double.parseDouble(df.format(dto.getMoneyNum()))));
				
				//sheet.addCell(color);
				sheet.addCell(name);
				sheet.addCell(total);
				sheet.addCell(piNum);
				sheet.addCell(meterNum);
				sheet.addCell(moneyNum);
				


				rowIndex++;
				
			}
			
			Label total = new Label(1,rowIndex,"总计");
			Number piString = new Number(2,rowIndex,pitotal);
			Number meterString = new Number(3,rowIndex,Double.parseDouble(dfone.format(metertotal)));
			Number moneyString = new Number(4,rowIndex,this.getNumerReg(Double.parseDouble(df.format(moneytotal))));
			
			sheet.addCell(total);
			sheet.addCell(piString);
			sheet.addCell(meterString);
			sheet.addCell(moneyString);
			excel.write();
			excel.close();
		}
	}
	private String getDateStr() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		return sdf.format(new Date());
	}
	
	private Double getNumerReg(double number){
		double result = number;
		String Snumber = new Double(number).toString();
		
		if(Snumber.substring(Snumber.lastIndexOf(".")+1, Snumber.length()).equals("999")){
			result = new Double( Snumber.substring(0,Snumber.lastIndexOf("."))) + 1;
			return result;
		}else if(Snumber.substring(Snumber.lastIndexOf(".")+2, Snumber.length()).equals("99")){
			result = new Double( Snumber.substring(0,Snumber.lastIndexOf(".")+2)) + 0.1;
			return result;
		}else{
			return result;
		}

	}
	public String getFilename() {
		return filename;
	}


	public void setFilename(String filename) {
		this.filename = filename;
	}


	public Integer getYear() {
		return year;
	}


	public void setYear(Integer year) {
		this.year = year;
	}


	public Integer getMonth() {
		return month;
	}


	public void setMonth(Integer month) {
		this.month = month;
	}

	public String getStartDate() {
		return startDate;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}

	public String getEndDate() {
		return endDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}

	public SaleOrderService getSaleOrderService() {
		return saleOrderService;
	}


	public void setSaleOrderService(SaleOrderService saleOrderService) {
		this.saleOrderService = saleOrderService;
	}

	public String getCustomerName() {
		return customerName;
	}

	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}

	public String getStaffName() {
		return staffName;
	}

	public void setStaffName(String staffName) {
		this.staffName = staffName;
	}

	
}
